#!/usr/bin/env python
# -*- coding:utf-8 -*-

import json
import requests
import os
import urllib2
import urlparse
import sqlite3


url = 'http://120.92.51.248/api/ad/list'
ad_list = []

def get_remote_ad_list():
	response = requests.get(url)
	#print response.text
	json_data = json.loads(response.text)
	for ad in json_data['data']:
		if ad['enable']:
			ad_list.append(ad)
	return ad_list

local_db = "D:\\python_shell\\local.db"
def init_db():
    try:
		conn = sqlite3.connect(local_db)
		c = conn.cursor()
		c.execute('''CREATE TABLE IF NOT EXISTS ad 
               (id INTEGER primary key AUTOINCREMENT,
                name text NOT NULL,
                company_id text,
                url text,
                cover text,
                ctime text,
                enable text, 
                type text )''')
		conn.commit()
    except Exception, e:
        print e
    finally:
        conn.close()


def store_to_db(ad):
	try:
		conn = sqlite3.connect(local_db)
		#print ad
		c = conn.cursor()
		#sql_text = "INSERT INTO ad (id, name, company_id, url, cover, ctime, enable, type) " \
		#" VALUES ('%s','%s','%s','%s','%s','%s','%s','%s') " \
		# % (ad['id'], ad['name'], ad['companyId'], ad['url'], ad['cover'], ad['ctime'], ad['enable'], ad['type'])
		
		sql_text = "INSERT INTO ad (id, name, company_id, url, cover, ctime, enable, type) " \
		" select '%s','%s','%s','%s','%s','%s','%s','%s' " \
		" where not exists (select * from ad where id='%s') " \
		 % (ad['id'], ad['name'], ad['companyId'], ad['url'], 
		 	ad['cover'], ad['ctime'], ad['enable'], ad['type'],
		 	ad['id'])
		#print sql_text
		c.execute(sql_text)
		conn.commit()
	except Exception, e:
		print e
	finally:
		conn.close()


def compare_time(id, ctime):	
    try:
    	ret = False
        conn = sqlite3.connect(local_db)
        cu   = conn.cursor()
        
        sql_text = "select count(1) from ad where id='%s' and ctime='%s'" % (id, ctime)
        #print sql_text
        cu.execute(sql_text)

        reset = cu.fetchall()
        print reset
        count = len(reset)
        if count>0:
        	print reset[0][0]
        	if reset[0][0]>0:
        		ret = True

    except Exception, e:
        print e
    finally:
        conn.close()
        return ret

# ensure make the dir
def ensure_dir(filename):
    dir = os.path.dirname(filename)
    if not os.path.exists(dir):
        try:
            os.makedirs(dir)
        except OSError:
            if not os.path.isdir(dir):
                raise

# delete the file
def delete_file(filename):
    if os.path.isfile(filename):
        try:
            os.remove(filename)
        except Exception,e:
            print e
            

# download the fiel from the internet.
def download_from_url(video_url, local_path):
    try:
        o = urlparse.urlparse(video_url)    
        filename = local_path.rstrip('/') + o.path
        
        delete_file(filename)
        ensure_dir(filename)
        print "source:%s" % video_url
        print "target:%s" % filename    

        f = urllib2.urlopen(video_url)
        with open(filename, "wb") as code:
            code.write(f.read())
        
        if os.path.exists(filename):
            return filename
        #play_file(filename)
    except Exception,e:
        #print Exception,":",e
        print e
    return None   
 
# play the video file.
def play_file(filename):
    try:
        cmd = "omxplayer %s" % filename
        os.system(cmd)
    except Exception,e:
        print e


def upgrade_ad_files():
	get_remote_ad_list()
	init_db()
	for ad in ad_list:
		#print ad
		if (compare_time(ad['id'], ad['ctime'])==False):
			print 'is different'
			filename = download_from_url(ad['url'], 'd:')
			store_to_db(ad)
			play_file(filename)

def main():
	upgrade_ad_files()
	
if __name__ == "__main__":
    main();
